﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

namespace CMSMVC4.Filters
{
    public class CommandSql
    {
        readonly SqlConnection _conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString);

        #region Ducnv
        #region Hàm sử lý (không sửa)

        private int _pagenumber;
        private int _count;

        /// <summary>
        /// Ham kiem tra trang da hop le chua
        /// </summary>
        /// <param name="page"> trang chek</param>
        /// <returns>trang hop le</returns>
        private int CheckPage(string page)
        {
            int curpage = 1;
            if (page != null)
            {
                if (Regex.IsMatch(page, "[0-9]+"))
                {
                    curpage = int.Parse(page.Trim());
                    if (curpage > _count)
                    {
                        curpage = _count;
                    }
                    if (curpage < 1)
                    {
                        curpage = 1;
                    }
                }
            }
            return curpage;
        }

        #endregion

        #region Hàm lấy dữ liệu phan trang (viết hàm vào đây)

        public int SetCountComment(int rownumber, int idpage)
        {
            _pagenumber = rownumber;
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = @"Select Count(*) AS Count FROM  admin.Coment WHERE (Page = @Page) AND (Status = " + CommentStatus.Confirm + ")";
            SqlCommand cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Page", idpage);
            da.SelectCommand = cmd;
            DataTable tb = new DataTable();
            da.Fill(tb);
            int countrows = int.Parse(tb.Rows[0]["Count"].ToString());
            _count = countrows % _pagenumber == 0 ? countrows / _pagenumber : countrows / _pagenumber + 1;
            return _count;
        }

        public DataTable GetDataComment(ref String pageindex, int idpage)
        {
            int page = CheckPage(pageindex);//kiểm tra trang đúng cú pháp chưa
            pageindex = page.ToString(CultureInfo.InvariantCulture);//set lại giá trị sau khi kiểm tra
            int start = page * _pagenumber - _pagenumber;
            int end = page * _pagenumber;
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = @"SELECT *FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id DESC)AS row,*
                            FROM  admin.Coment
                            WHERE (Page = @Page) AND (Status = " + CommentStatus.Confirm + @"))AS LogWithRowNumbers WHERE Row > " + start + " AND Row <= " + end;
            SqlCommand cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Page", idpage);
            da.SelectCommand = cmd;
            DataTable tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        public int SetCountTinKhac(int rownumber, int idcate)
        {
            string culture = GetLanguage();
            _pagenumber = rownumber;
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = @"Select Count(*) AS Count FROM  
                                        admin.Categories INNER JOIN
                                        admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                                        admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId 
                            WHERE       (admin.Pages.CategoriesID = @CategoriesID) AND (admin.PageLang.LanguageCode = @culture)  AND (admin.Pages.Status <> @status)";
            SqlCommand cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@CategoriesID", idcate);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            DataTable tb = new DataTable();
            da.Fill(tb);
            int countrows = int.Parse(tb.Rows[0]["Count"].ToString());
            //if (countrows >= 3)//loai bo 3 thang tren dau
            //{
            //    countrows = countrows - 3;
            //}
            _count = countrows % _pagenumber == 0 ? countrows / _pagenumber : countrows / _pagenumber + 1;
            return _count;
        }

        public DataTable GetDataTinKhac(ref String pageindex, int idcate)
        {
            string culture = GetLanguage();
            int page = CheckPage(pageindex);//kiểm tra trang đúng cú pháp chưa
            pageindex = page.ToString(CultureInfo.InvariantCulture);//set lại giá trị sau khi kiểm tra
            int start = page * _pagenumber - _pagenumber;
            int end = page * _pagenumber;
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = @"SELECT *FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Date] DESC)AS row,
                            admin.PageLang.PageId, admin.PageLang.Name, admin.PageLang.Summary, admin.Pages.Img,admin.Pages.[Date]
                            FROM        admin.Categories INNER JOIN
                                        admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                                        admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId 
                            WHERE       (admin.Pages.CategoriesID = @CategoriesID) AND (admin.PageLang.LanguageCode = @culture) AND (admin.Pages.Status <> @status))AS LogWithRowNumbers WHERE Row > " + start + " AND Row <= " + end;
            SqlCommand cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@CategoriesID", idcate);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            DataTable tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        public int SetCountTimKiem(int rownumber, string tukhoa)
        {
            string tukhoacd = tukhoa;
            tukhoa = ConvertToUnSign(tukhoa);
            _pagenumber = rownumber;
            SqlDataAdapter da = new SqlDataAdapter();
            string sql =
                @"Select Count(*) AS Count FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Id] DESC)AS row ,* FROM (
(SELECT        admin.Categories.Id, admin.Categories.Type, admin.CateLang.Name,'NULL' AS Img
FROM            admin.Categories INNER JOIN
                         admin.CateLang ON admin.Categories.Id = admin.CateLang.CategoriesId INNER JOIN
						 admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                         admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
WHERE        (admin.Categories.Status = 1) AND (admin.Categories.Type <> 3)  AND ((admin.ConvertToUnSign(admin.CateLang.Name) LIKE @tukhoa OR (admin.PageLang.[FullContent] LIKE @tukhoacd))))
union
(SELECT        admin.Pages.Id,admin.Categories.Type, admin.PageLang.Name, admin.Pages.Img
FROM            admin.Categories INNER JOIN
                         admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                         admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
WHERE        (admin.Categories.Type = 3) AND (admin.Pages.Status <> 2) AND ((admin.ConvertToUnSign(admin.PageLang.Name) LIKE @tukhoa) OR (admin.PageLang.[FullContent] LIKE @tukhoacd)) )
) AS LogWithRowNumbers) AS ABC";
            SqlCommand cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@tukhoa", "%" + tukhoa + "%");
            cmd.Parameters.AddWithValue("@tukhoacd", "%" + tukhoacd + "%");
            da.SelectCommand = cmd;
            DataTable tb = new DataTable();
            da.Fill(tb);
            int countrows = int.Parse(tb.Rows[0]["Count"].ToString());
            _count = countrows % _pagenumber == 0 ? countrows / _pagenumber : countrows / _pagenumber + 1;
            return _count;
        }

        public DataTable GetDataTimKiem(ref String pageindex, string tukhoa)
        {
            string tukhoacd = tukhoa;
            tukhoa = ConvertToUnSign(tukhoa);
            int page = CheckPage(pageindex);//kiểm tra trang đúng cú pháp chưa
            pageindex = page.ToString(CultureInfo.InvariantCulture);//set lại giá trị sau khi kiểm tra
            int start = page * _pagenumber - _pagenumber;
            int end = page * _pagenumber;
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Id] DESC)AS row ,* FROM (
(SELECT        admin.Categories.Id, admin.Categories.Type, admin.CateLang.Name,'NULL' AS Img
FROM            admin.Categories INNER JOIN
                         admin.CateLang ON admin.Categories.Id = admin.CateLang.CategoriesId INNER JOIN
						 admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                         admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
WHERE        (admin.Categories.Status = 1) AND (admin.Categories.Type <> 3)  AND ((admin.ConvertToUnSign(admin.CateLang.Name) LIKE @tukhoa OR (admin.PageLang.[FullContent] LIKE @tukhoacd))))
union
(SELECT        admin.Pages.Id,admin.Categories.Type, admin.PageLang.Name, admin.Pages.Img
FROM            admin.Categories INNER JOIN
                         admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                         admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
WHERE        (admin.Categories.Type = 3) AND (admin.Pages.Status <> 2) AND ((admin.ConvertToUnSign(admin.PageLang.Name) LIKE @tukhoa) OR (admin.PageLang.[FullContent] LIKE @tukhoacd)) )
) AS LogWithRowNumbers) AS ABC WHERE ABC.Row > " + start + " AND ABC.Row <= " + end;
            SqlCommand cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@tukhoa", "%" + tukhoa + "%");
            cmd.Parameters.AddWithValue("@tukhoacd", "%" + tukhoacd + "%");
            da.SelectCommand = cmd;
            DataTable tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        #endregion

        public string GetLanguage()
        {
            string culture = "vi-vn";
            var httpCookie = HttpContext.Current.Request.Cookies["language"];
            if (httpCookie != null)
            {
                culture = httpCookie.Value;
            }
            return culture;
        }

        public DataTable GetMenuParrent(int area , string role)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT   admin.Menu.Id, admin.MenuLang.Name, admin.Menu.Url, admin.Menu.Type, admin.Menu.Parrent, admin.Menu.Icon, admin.Menu.Image, admin.Menu.Sort, 
                                    admin.MenuLang.Description
                            FROM    admin.Menu INNER JOIN
                                    admin.MenuLang ON admin.Menu.Id = admin.MenuLang.MenuId INNER JOIN
                                    admin.Languages ON admin.MenuLang.LanguageCode = admin.Languages.Code
                            WHERE   ((SELECT COUNT(*) AS 'Count' FROM (SELECT Value FROM admin.SPLIT(',',admin.Menu.Role)) AS A
inner join (SELECT Value FROM admin.SPLIT(',',@role)) AS B ON A.Value = B.Value) > 0) AND (admin.Menu.Parrent = 0) AND (admin.Menu.Area = @Area) AND (admin.Languages.Code = @culture) AND (admin.Menu.Sort <> 6)
                            ORDER BY admin.Menu.Sort";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Area", area);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@role", role);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        public DataTable GetMenuParrent2(int area)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            var sql = @"SELECT   admin.Menu.Id, admin.MenuLang.Name, admin.Menu.Url, admin.Menu.Type, admin.Menu.Parrent, admin.Menu.Icon, admin.Menu.Image, admin.Menu.Sort, 
                                    admin.MenuLang.Description
                            FROM    admin.Menu INNER JOIN
                                    admin.MenuLang ON admin.Menu.Id = admin.MenuLang.MenuId INNER JOIN
                                    admin.Languages ON admin.MenuLang.LanguageCode = admin.Languages.Code
                            WHERE   (admin.Menu.Parrent = 0) AND (admin.Menu.Area = @Area) AND (admin.Languages.Code = @culture) AND ((admin.Menu.Type = " + MenuType.DichVu + @") OR (admin.Menu.Type = " + MenuType.GioiThieu + @") OR (admin.Menu.Type = " + MenuType.ThiteBi + @") OR (admin.Menu.Type = " + MenuType.DoiTac + @"))
                            ORDER BY admin.Menu.Sort";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Area", area);
            cmd.Parameters.AddWithValue("@culture", culture);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        public DataTable GetMenuChil(int parent, int area)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT   admin.Menu.Id, admin.MenuLang.Name, admin.Menu.Url, admin.Menu.Type, admin.Menu.Parrent, admin.Menu.Icon, admin.Menu.Image, admin.Menu.Sort, 
                                    admin.MenuLang.Description
                            FROM    admin.Menu INNER JOIN
                                    admin.MenuLang ON admin.Menu.Id = admin.MenuLang.MenuId INNER JOIN
                                    admin.Languages ON admin.MenuLang.LanguageCode = admin.Languages.Code
                            WHERE   (admin.Menu.Parrent = @Parrent) AND (admin.Menu.Area = @Area) AND (admin.Languages.Code = @culture)
                            ORDER BY admin.Menu.Sort";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Area", area);
            cmd.Parameters.AddWithValue("@Parrent", parent);
            cmd.Parameters.AddWithValue("@culture", culture);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// Lay Noi Dung Tom Tat Theo Loai
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable GetSummary(int type)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            var sql = @"SELECT  TOP (1)  admin.PageLang.Summary
                            FROM        admin.Categories INNER JOIN
                                        admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                                        admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId INNER JOIN
                                        admin.Languages ON admin.PageLang.LanguageCode = admin.Languages.Code
                            WHERE       (admin.Pages.Status = " + PageStatus.GioiThieu + @") AND (admin.Languages.Code = @culture) AND (admin.Categories.Type = @Type) AND (admin.Pages.Status <> @status)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Type", type);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// Lay Danh Sach Ten Categories theo loai
        /// </summary>
        /// <param name="type">loai</param>
        /// <returns>danh sach</returns>
        public DataTable GetNameCategories(int type)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT   admin.Categories.Id, admin.CateLang.Name,admin.Categories.paren
                            FROM    admin.Categories INNER JOIN
                                    admin.CateLang ON admin.Categories.Id = admin.CateLang.CategoriesId
                            WHERE   (admin.Categories.Type = @Type) AND (admin.CateLang.LanguageCode = @culture) AND 
                                    (admin.Categories.Status = @Status) AND (admin.Categories.paren = 0)
                            ORDER BY admin.Categories.Sort DESC";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Type", type);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@Status", CategoriStatus.Thuong);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// select bai viet theo ma categori
        /// </summary>
        /// <param name="id">ma categori</param>
        /// <returns>bai viet</returns>
        public DataTable GetPageLang(int id)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT TOP (1) admin.Pages.CategoriesID, admin.PageLang.PageId, admin.PageLang.FullContent, admin.PageLang.Summary, admin.PageLang.Name
                                    FROM  admin.Pages INNER JOIN
                                          admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
                            WHERE        (admin.PageLang.LanguageCode = @culture) AND (admin.Pages.CategoriesID = @Id) AND (admin.Pages.Status <> @status)
                            ORDER BY admin.Pages.Tab";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// Lay ID categorie theo loai va trang thai bai viet
        /// </summary>
        /// <param name="type"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public DataTable GetIdCategorieByStatus(int type, int status)
        {
            var da = new SqlDataAdapter();
            const string sql = @"SELECT  TOP (1)  admin.Categories.Id
                            FROM            admin.Categories INNER JOIN
                                            admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID
                            WHERE           (admin.Categories.Type = @Type) AND (admin.Pages.Status = @status)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Type", type);
            cmd.Parameters.AddWithValue("@status", status);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// Lay Danh Sach 5 Tin Tuc Moi Nhat
        /// </summary>
        /// <returns></returns>
        public DataTable GetNewsHot()
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            var sql = @"SELECT TOP (5) admin.PageLang.Name, admin.Pages.Id
                            FROM          admin.Categories INNER JOIN
                                          admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                                          admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId INNER JOIN
                                          admin.Languages ON admin.PageLang.LanguageCode = admin.Languages.Code
                            WHERE        (admin.Categories.Type = " + MenuType.TinTuc + @") AND (admin.Languages.Code = @culture) AND (admin.Pages.Status <> @status)
                            ORDER BY admin.Pages.Date DESC";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// Lay 3 Bai Viet Duoc Xem Nhieu Nhat
        /// </summary>
        /// <param name="idcate">ma danh muc</param>
        /// <returns>3 tin tuc doc nhieu nhat</returns>
        public DataTable GetBaiVietXemNhieuNhat(int idcate)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT        TOP (3) admin.PageLang.PageId, admin.PageLang.Name, admin.PageLang.Summary, admin.Pages.Img
                            FROM            admin.Categories INNER JOIN
                                        admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                                        admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId 
                            WHERE       (admin.Pages.CategoriesID = @CategoriesID) AND (admin.PageLang.LanguageCode = @culture) AND (admin.Pages.Status <> @status)
                            ORDER BY admin.Pages.[Detail] DESC";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@CategoriesID", idcate);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// Get Dia chi lien he
        /// </summary>
        /// <returns></returns>
        public DataTable GetConntact()
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT        admin.Contact.*, admin.ContactLang.Name, admin.ContactLang.Address,admin.ContactLang.Description
                            FROM         admin.Contact INNER JOIN
                                         admin.ContactLang ON admin.Contact.Branch = admin.ContactLang.ContactBranch
                            WHERE        (admin.ContactLang.LanguageCode = @culture)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@culture", culture);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        /// <summary>
        /// select list bai viet theo ma categori
        /// </summary>
        /// <param name="id">ma categori</param>
        /// <returns>bai viet</returns>
        public DataTable GetListPageLag(int id)
        {
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"SELECT  admin.Pages.CategoriesID, admin.PageLang.PageId, admin.PageLang.FullContent, admin.PageLang.Name
                                    FROM  admin.Pages INNER JOIN
                                          admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
                            WHERE        (admin.PageLang.LanguageCode = @culture) AND (admin.Pages.CategoriesID = @Id) AND (admin.Pages.Status <> @status)
                            ORDER BY admin.Pages.Tab";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@culture", culture);
            cmd.Parameters.AddWithValue("@status", PageStatus.Nhap);
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        //convert chu co dau thanh ko dau
        public static string ConvertToUnSign(string s)
        {
            Regex regex = new Regex("\\p{IsCombiningDiacriticalMarks}+");
            string temp = s.Normalize(NormalizationForm.FormD);
            return regex.Replace(temp, String.Empty).Replace('\u0111', 'd').Replace('\u0110', 'D');
        }  

        /// <summary>
        /// Ham Tim Kiem Cua Admin
        /// </summary>
        /// <param name="name">tu khoa</param>
        /// <returns>ket qua</returns>
        public DataTable SearchAdmin(string name)
        {
            name = ConvertToUnSign(name);
            var culture = GetLanguage();
            var da = new SqlDataAdapter();
            const string sql = @"(SELECT distinct admin.CateLang.CategoriesId AS Id, admin.CateLang.Name,admin.Categories.Type,0 AS PageId
                                FROM         admin.CateLang INNER JOIN
                                             admin.Categories ON admin.CateLang.CategoriesId = admin.Categories.Id
                                WHERE        ([admin].[ConvertToUnSign](admin.CateLang.Name) LIKE @name) AND (admin.Categories.Status <> 0) AND (admin.Categories.Type <> 3) AND (admin.Categories.Type <> 4))
                                union
                                (SELECT  distinct     admin.Pages.CategoriesID AS Id, admin.PageLang.Name,admin.Categories.Type,admin.PageLang.PageId
                                FROM         admin.PageLang INNER JOIN
                                             admin.Pages ON admin.PageLang.PageId = admin.Pages.Id INNER JOIN
                                             admin.Categories ON admin.Pages.CategoriesID = admin.Categories.Id
                                WHERE        ([admin].[ConvertToUnSign](admin.PageLang.Name) LIKE @name) AND (admin.Pages.Status <> 2)  AND (admin.Categories.Type = 3) AND (admin.Categories.Type <> 4))";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@name", "%"+name+"%");
            da.SelectCommand = cmd;
            var tb = new DataTable();
            da.Fill(tb);
            return tb;
        }

        #endregion

        #region Camnv
        // Lấy dữ liệu đầu tiên bảng Contact
        public DataTable GetContactDesc()
        {
            const string sql = @"SELECT TOP 1 [Branch]
                                ,[Phone]
                                ,[Fax]
                                ,[Email]
                                ,[Website]
                            FROM [CMSMVC4].[admin].[Contact]
                            ORDER BY [Branch] DESC";

            var cmd = new SqlCommand(sql, _conn);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Lấy dữ liệu hiển thị thông tin liên lạc
        public DataTable GetAllContacts()
        {
            var culture = GetLanguage();
            const string sql = @"SELECT  admin.Contact.Branch, admin.Contact.Phone, admin.Contact.Fax, admin.Contact.Email, 
                                admin.Contact.Website, admin.ContactLang.Name, 
                                admin.ContactLang.Address
                          FROM  admin.Contact INNER JOIN admin.ContactLang ON admin.Contact.Branch = admin.ContactLang.ContactBranch
                          WHERE (admin.ContactLang.LanguageCode = @culture)";

            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@culture", culture);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Lấy dữ liệu từ Contact vs ContactLang để Edit
        public DataTable GetOneContact(int branch)
        {
            var culture = GetLanguage();
            const string sql = @"SELECT admin.Contact.Phone, admin.Contact.Fax, admin.Contact.Email, admin.Contact.Url,
                                        admin.Contact.Website, admin.ContactLang.ContactBranch, admin.ContactLang.Name, 
                                        admin.ContactLang.Address
                                FROM    admin.Contact INNER JOIN
                                        admin.ContactLang ON admin.Contact.Branch = admin.ContactLang.ContactBranch
                                WHERE  (admin.Contact.Branch = @branch) AND (admin.ContactLang.LanguageCode = @culture)";

            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@branch", branch);
            cmd.Parameters.AddWithValue("@culture", culture);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }


        // Get address by language
        public DataTable GetOneContactByLang(int branch, string lang)
        {
            const string sql = @"SELECT admin.Contact.Phone, admin.Contact.Fax, admin.Contact.Email, admin.Contact.Website, admin.ContactLang.ContactBranch, admin.ContactLang.Name, 
                                        admin.ContactLang.Address
                                FROM    admin.Contact INNER JOIN
                                        admin.ContactLang ON admin.Contact.Branch = admin.ContactLang.ContactBranch
                                WHERE  (admin.Contact.Branch = @branch) AND (admin.ContactLang.LanguageCode = @culture)";

            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@branch", branch);
            cmd.Parameters.AddWithValue("@culture", lang);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Xem thông tin Feedback
        public DataTable GetAllFeedback()
        {
            const string sql = @"SELECT admin.Feedback.* FROM admin.Feedback Order By admin.Feedback.Id";
            var cmd = new SqlCommand(sql, _conn);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Get Feedback by ID
        public DataTable GetFeedbackById(int id)
        {
            const string sql = @"SELECT Id, Name, Email, Phone, Subject, Message FROM admin.Feedback WHERE (Id = @id)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@id", id);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Get Top 1 contact by desc
        public DataTable GetTopOneContact()
        {
            const string sql = @"SELECT TOP (1) PERCENT Id, Branch, Phone, Fax, Email, Website
                                    FROM        admin.Contact
                                    ORDER BY Branch DESC";

            var cmd = new SqlCommand(sql, _conn);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }


        public DataTable GetParrentByMenu(string url)
        {
            const string sql = @"SELECT   Id, Parrent
                                FROM      admin.Menu
                                WHERE     (Url = @url)";

            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@url", url);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Get All News
        public DataTable GetAllNews()
        {
            const string sql = @"SELECT     admin.Pages.Id,admin.PageLang.Name, admin.Categories.Type, admin.CateLang.Name AS Expr1, admin.CateLang.LanguageCode, admin.PageLang.LanguageCode AS Expr2, 
                                            admin.PageLang.FullContent, admin.Pages.Date
                                    FROM    admin.Categories INNER JOIN
                                            admin.CateLang ON admin.Categories.Id = admin.CateLang.CategoriesId INNER JOIN
                                            admin.Pages ON admin.Categories.Id = admin.Pages.CategoriesID INNER JOIN
                                            admin.PageLang ON admin.Pages.Id = admin.PageLang.PageId
                                    WHERE   (admin.Categories.Type = @type) AND (admin.CateLang.LanguageCode = @culture) AND (admin.PageLang.LanguageCode = @culture) AND (admin.Pages.Status = @Status)
                                    ORDER BY admin.Pages.Date DESC";

            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@Status", PageStatus.Thuong);
            cmd.Parameters.AddWithValue("@type", MenuType.TinTuc);
            cmd.Parameters.AddWithValue("@culture", GetLanguage());
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public DataTable GetCategoryIdDesc(int id)
        {
            const string sql = @"SELECT     TOP (1) admin.Pages.Id, admin.Categories.Type
FROM                                        admin.Pages INNER JOIN
                                            admin.Categories ON admin.Pages.CategoriesID = admin.Categories.Id
WHERE                                       (admin.Categories.Type = @type) AND (admin.Pages.Id = @id)
ORDER BY                                    admin.Pages.Id DESC";

            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@type", MenuType.TinTuc);
            cmd.Parameters.AddWithValue("@id", id);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var talbe = new DataTable();
            adapter.Fill(talbe);
            return talbe;
        }

        // Support Online
        public DataTable GetAllSupport()
        {
            const string sql = @"SELECT admin.Introduction.* FROM admin.Introduction";
            var cmd = new SqlCommand(sql, _conn);
            var adapter = new SqlDataAdapter {SelectCommand = cmd};
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Get Background
        public  DataTable GetAllBackground()
        {
            const string sql = @"SELECT        admin.Baner.*
                          FROM        admin.Baner
                         WHERE        (Type = @status)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@status", BanerStatus.Background);
            var adapter = new SqlDataAdapter {SelectCommand = cmd};
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        // Get Baner Top
        public DataTable GetBanerTop()
        {
            const string sql = @"SELECT        admin.Baner.*, Status
                        FROM          admin.Baner
                        WHERE        (Status < 3)";
            var cmd = new SqlCommand(sql, _conn);
            var adapter = new SqlDataAdapter {SelectCommand = cmd};
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        //Get baner GioiThieu
        public  DataTable GetBanerGoiThieu()
        {
            const string sql = @"SELECT        admin.Baner.*, Location AS Expr1, Type AS Expr2
                        FROM            admin.Baner
                        WHERE        (Location <> @location) AND (Type = @type)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@location", BanerStatus.Lienhe);
            cmd.Parameters.AddWithValue("@type", BanerType.GioiThieu);
            var adapter = new SqlDataAdapter {SelectCommand = cmd};
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public DataTable GetBanerThietBi()
        {
            const string sql = @"SELECT        admin.Baner.*, Location AS Expr1, Type AS Expr2
                        FROM            admin.Baner
                        WHERE        (Location <> @location) AND (Type = @type)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@location", BanerStatus.Lienhe);
            cmd.Parameters.AddWithValue("@type", BanerType.ThietBi);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public DataTable GetBanerDichVu()
        {
            const string sql = @"SELECT        admin.Baner.*, Location AS Expr1, Type AS Expr2
                        FROM            admin.Baner
                        WHERE        (Location <> @location) AND (Type = @type)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@location", BanerStatus.Lienhe);
            cmd.Parameters.AddWithValue("@type", BanerType.DichVu);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public DataTable GetBanerTinTuc()
        {
            const string sql = @"SELECT        admin.Baner.*, Location AS Expr1, Type AS Expr2
                        FROM            admin.Baner
                        WHERE        (Location <> @location) AND (Type = @type)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@location", BanerStatus.Lienhe);
            cmd.Parameters.AddWithValue("@type", BanerType.TinTuc);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public DataTable GetBanerLienHe()
        {
            const string sql = @"SELECT        Id, Name, Url, Status, Type, Location, Location AS Expr1, Type AS Expr2
                                 FROM            admin.Baner
                                 WHERE        (Type = 6)";
            var cmd = new SqlCommand(sql, _conn);
            cmd.Parameters.AddWithValue("@location", BanerStatus.Lienhe);
            var adapter = new SqlDataAdapter { SelectCommand = cmd };
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }
        #endregion
    }
}